const log4js = require('log4js');
const logger = log4js.getLogger('log');
const config = require('../config');
const { Pool } = require('pg');

const pool = new Pool(config.db);

const DataProcess = {
    async saveBatch (body, options) {
        delete body['db'];
        delete body['table'];
        const keys = Object.keys(body);
        
        if (!keys.length) {
            logger.warn('传输过来的数据为空')
            return 1;
        } else {
            logger.info('数据行数：', keys.length);

            let client = await pool.connect();
            try {
                const {db, table, columns} = options;
                let sql = `insert into ${db}.${table} (`;
                let valueSql = [];
                const cols = columns.split(',');
                for (let i in cols) {
                    sql += cols[i] + ',';
                    valueSql.push('$'+(parseInt(i)+1));
                }
                sql = sql.substr(0, sql.length - 1);
                sql += ') values ('+valueSql.join(',')+')';

                for (let i = 0; i < keys.length; i++) {
                    const row = body[keys[i]];
                    const values = DataProcess.saveLine(row, cols, i);
                    await client.query(sql, values);
                }

                logger.debug('sql:', sql);
                return 1;
            } catch (e) {
                // 回滚
                logger.error('出现错误', e);
            } finally {
                await client.end();
            }
            logger.info('数据录完');
            return -1;
        }
    },

    /**
     * 更新或插入
     * @param {*} body 
     * @param {*} options 
     * @returns 
     */
    async updateBatch  (body, options) {
        delete body['db'];
        delete body['table'];
        const keys = Object.keys(body);
        
        if (!keys.length) {
            logger.warn('传输过来的数据为空')
            return 1;
        } else {
            logger.info('数据行数：', keys.length);

            let client = await pool.connect();
            try {
                const {db, table, columns, primaryField} = options;
                let sql = `insert into ${db}.${table} (`;
                const cols = columns.split(',');
                for (let i in cols) {
                    sql += cols[i] + ',';
                }
                sql = sql.substr(0, sql.length - 1);
                sql += ') values';

                const arr = [];
                for (let i = 0; i < keys.length; i++) {
                    const row = body[keys[i]];
                    if (row) {
                        let uSql = DataProcess.updateLine(row, cols, i);
                        let insertOrUpdateSql = sql += uSql;
                        insertOrUpdateSql += ' ON CONFLICT ('+primaryField+') DO UPDATE SET ' ;
                        const uSql2 = DataProcess.updateLine2(row, cols, i);
                        insertOrUpdateSql += uSql2;
                        logger.debug('sql:', insertOrUpdateSql);
                        arr.push(client.query(insertOrUpdateSql));
                    }
                }

                const rets = await Promise.all(arr);
                return rets;
            } catch (e) {
                logger.error('出现错误', e);
            } finally {
                await client.end();
            }
            logger.info('数据录完');
            return -1;
        }
    },

    /**
     * 一行一行保存记录
     * @param {*} line 
     * @param {*} index 第几行
     */
    saveLine (line, cols, index) {
        if (line) {
            logger.debug('数据 ', index, ' : ', line);
            const values = [];
            for (let i in cols) {
                const field = cols[i];
                const val = line[field];
                values.push(val);
            }
            
            return values;
        }
    },

    updateLine (line, cols, index) {
        let sql = '';
        if (line) {
            logger.debug('数据 ', index, ' : ', line);
            const values = [];
            for (let i in cols) {
                const field = cols[i];
                const val = line[field];
                if (typeof val === 'string') {
                    values.push(`'${val}'`);
                } else if (val === null || val === undefined) {
                    values.push('NULL');
                } else {
                    values.push(val);
                }
            }
            
            sql = `(${values.join(',')})`;
        }
        return sql;
    },

    updateLine2 (line, cols) {
        let sql = '';
        if (line) {
            const values = [];
            for (let i in cols) {
                const field = cols[i];
                const val = line[field];
                if (typeof val === 'string') {
                    values.push(`${field}='${val}'`);
                } else if (val === null || val === undefined) {
                    values.push(field + '=NULL');
                } else {
                    values.push(field + '=' + val);
                }
            }
            
            sql = `(${values.join(',')})`;
        }
        return sql;
    }
}

module.exports = DataProcess;